package com.CommunityHub.kiana.dao;



import com.CommunityHub.kiana.po.ServiceRecord;
import com.CommunityHub.kiana.po.ServiceResidence;
import com.CommunityHub.kiana.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ServiceRecordDAO {
    public List<ServiceRecord> findByMemberId(int memberId) throws SQLException {
        List<ServiceRecord> records = new ArrayList<>();
        String sql = "SELECT sr.* FROM service_record sr "
                + "JOIN residence_info ri ON sr.residence_id = ri.residence_id "
                + "WHERE ri.member_id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, memberId);
            rs = ps.executeQuery();

            while (rs.next()) {
                ServiceRecord record = mapServiceRecord(rs);
                records.add(record);
            }
            return records;
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }

    public List<ServiceResidence> findByMemberById(int memberId) throws SQLException {
        List<ServiceResidence> records = new ArrayList<>();
        String sql = "SELECT sr.*, occupant_name FROM service_record sr "
                + "JOIN residence_info ri ON sr.residence_id = ri.residence_id "
                + "WHERE ri.member_id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, memberId);
            rs = ps.executeQuery();

            while (rs.next()) {
                ServiceResidence record = mapServiceResidence(rs);
                //record.setOccupantName(rs.getString("occupant_name"));
                records.add(record);
            }
            return records;
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }

    // 添加服务记录
    public boolean addRecord(ServiceRecord record) throws SQLException {
        String sql = "INSERT INTO service_record (steward_id, residence_id, service_type, "
                + "service_time, service_duration, service_status) "
                + "VALUES (?, ?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, record.getStewardId());
            ps.setInt(2, record.getResidenceId());
            ps.setString(3, record.getServiceType());
            ps.setTimestamp(4, new java.sql.Timestamp(record.getServiceTime().getTime()));
            ps.setInt(5, record.getServiceDuration());
            ps.setInt(6, record.getServiceStatus());

            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        } finally {
            DBUtil.close(conn, ps, null);
        }
    }

    private ServiceResidence mapServiceResidence(ResultSet rs) throws SQLException {
        ServiceResidence record = new ServiceResidence();
        record.setRecordId(rs.getInt("record_id"));
        record.setStewardId(rs.getInt("steward_id"));
        record.setResidenceId(rs.getInt("residence_id"));
        record.setServiceType(rs.getString("service_type"));
        record.setServiceTime(rs.getTimestamp("service_time"));
        record.setServiceDuration(rs.getInt("service_duration"));
        record.setServiceStatus(rs.getInt("service_status"));
        record.setOccupantName(rs.getString("occupant_name"));
        return record;
    }

    private ServiceResidence mapServiceRecord(ResultSet rs) throws SQLException {
        ServiceResidence record = new ServiceResidence();
        record.setRecordId(rs.getInt("record_id"));
        record.setStewardId(rs.getInt("steward_id"));
        record.setResidenceId(rs.getInt("residence_id"));
        record.setServiceType(rs.getString("service_type"));
        record.setServiceTime(rs.getTimestamp("service_time"));
        record.setServiceDuration(rs.getInt("service_duration"));
        record.setServiceStatus(rs.getInt("service_status"));
        return record;
    }
}
